package com.hulu.yec.teach.examplan.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hulu.yec.common.exception.DaoRuntimeException;
import com.hulu.yec.teach.examplan.entity.ExamPlanEntity;
import com.hulu.yec.teach.examplan.servlet.ExamPlanServlet;

public class util {

	final static String DRIVER = "com.mysql.jdbc.Driver";
	final static String URL = "jdbc:mysql://192.168.12.252:3306/exam";
	final static String USERNAME = "root";
	final static String PASSWORD = "root";

	/**
	 * 加载驱动连接数据库
	 * 
	 * @return
	 * @throws ClassNotFoundException
	 */
	private static Connection getConnection() throws ClassNotFoundException {
		Connection connection = null;

		try {
			Class.forName(DRIVER);
			connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (SQLException e) {
			throw new DaoRuntimeException("连接数据库失败");
		}
		return connection;
	}

	/**
	 * 执行简单SQL的增，删，改
	 * 
	 * @throws ClassNotFoundException
	 * 
	 */
	public static int executeUpdate(String sql, ExamPlanServlet... parameters)
			throws ClassNotFoundException {
		int row = 0;

		Connection conn = getConnection();
		PreparedStatement pst = null;

		try {
			pst = conn.prepareStatement(sql);
			// 判断是否设置参数
			if (parameters != null && parameters.length > 0) {
				for (int i = 0; i < parameters.length; i++) {
					pst.setObject(i + 1, parameters[i]);
				}
			}

			row = pst.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(pst, conn);
		}

		return row;
	}

	/**
	 * 分页执行查询SQL指令操作
	 * 
	 * @param ExamPlanEntity
	 * 
	 * @throws ClassNotFoundException
	 */
	public static PageModel<ExamPlanEntity> executeQuery(String sql,
			String sqll, int pageNo, int pageSize, ExamPlanEntity ExamPlanEntity)
			throws ClassNotFoundException {

		List<ExamPlanEntity> examplanList = new ArrayList<>();

		Connection conn = getConnection();
		PreparedStatement pst = null;
		ResultSet resultSet = null;

		try {
			pst = conn.prepareStatement(sqll);
			resultSet = pst.executeQuery();

			while (resultSet.next()) {
				ExamPlanEntity eplan = new ExamPlanEntity();
				eplan.setId(resultSet.getInt("id"));
				eplan.setExam_Time_Start(resultSet
						.getTimestamp("exam_time_start"));
				eplan.setExam_Time_Stop(resultSet
						.getTimestamp("exam_time_stop"));
				eplan.setExam_Classroom(resultSet.getString("exam_Classroom"));
				eplan.setOperate_user_id(resultSet.getInt("operate_user_id"));
				eplan.setName(resultSet.getString("name"));
				eplan.setState(resultSet.getShort("state"));
				examplanList.add(eplan);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(resultSet, pst, conn);
		}
		// 获取总行数
		int allRecords = getallRecords(ExamPlanEntity, sql);
		return new PageModel<ExamPlanEntity>(examplanList, pageNo, pageSize,
				allRecords);
	}

	/**
	 * 返回查询到的中行数
	 * 
	 * @param examPlanEntity
	 * @param sql
	 * @return
	 * @throws ClassNotFoundException
	 */
	private static int getallRecords(ExamPlanEntity examPlanEntity, String sql)
			throws ClassNotFoundException {
		Connection conn = getConnection();
		PreparedStatement pst = null;
		ResultSet resultSet = null;
		try {
			pst = conn.prepareStatement(sql);
			resultSet = pst.executeQuery();

			resultSet.next();
			return resultSet.getInt("count(*)");
		} catch (SQLException e) {
			throw new DaoRuntimeException("总条数的请求数据库错误！");
		} finally {
			close(resultSet, pst, conn);
		}
	}

	/**
	 * 详情查询SQL指令操作
	 * 
	 * @param ExamPlanEntity
	 * 
	 * @throws ClassNotFoundException
	 */
	public static List<ExamPlanEntity> executeQuery(String sql)
			throws ClassNotFoundException {

		List<ExamPlanEntity> examplanList = new ArrayList<>();

		Connection conn = getConnection();
		PreparedStatement pst = null;
		ResultSet resultSet = null;

		try {
			pst = conn.prepareStatement(sql);
			resultSet = pst.executeQuery();

			while (resultSet.next()) {
				ExamPlanEntity eplan = new ExamPlanEntity();
				eplan.setId(resultSet.getInt("id"));
				eplan.setExam_Time_Start(resultSet
						.getTimestamp("exam_time_start"));
				eplan.setExam_Time_Stop(resultSet
						.getTimestamp("exam_time_stop"));
				eplan.setExam_Classroom(resultSet.getString("exam_Classroom"));
				eplan.setId1(resultSet.getInt("id1"));
				eplan.setPaperName(resultSet.getString("paperName"));
				eplan.setId2(resultSet.getInt("id2"));
				eplan.setClassName(resultSet.getString("className"));
				eplan.setId3(resultSet.getInt("id3"));
				eplan.setManagerName(resultSet.getString("managerName"));
				eplan.setOperate_time(resultSet.getTimestamp("operate_time"));
				eplan.setState(resultSet.getShort("state"));
				eplan.setMemo(resultSet.getString("memo"));
				examplanList.add(eplan);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(resultSet, pst, conn);
		}
		return examplanList;
	}

	/**
	 * 查询全部补考ID集合
	 * 
	 * @param examPlanEntity
	 * @param sql
	 * @return
	 * @throws ClassNotFoundException
	 */
	public static List<ExamPlanEntity> executeQueryUsersId(String sql)
			throws ClassNotFoundException {
		List<ExamPlanEntity> usersId = new ArrayList<>();
		Connection conn = getConnection();
		PreparedStatement pst = null;
		ResultSet resultSet = null;

		try {
			pst = conn.prepareStatement(sql);
			resultSet = pst.executeQuery();
			while (resultSet.next()) {
				ExamPlanEntity eplan = new ExamPlanEntity();

				eplan.setTo_user_ids(resultSet.getString("to_user_ids"));
				usersId.add(eplan);
			}
			return usersId;
		} catch (SQLException e) {
			throw new DaoRuntimeException("总条数的请求数据库错误！");
		} finally {
			close(resultSet, pst, conn);
		}
	}

	/**
	 * 查询全部补考人员及相对应的Id
	 * 
	 * @param examPlanEntity
	 * @param sql
	 * @return
	 * @throws ClassNotFoundException
	 */
	public static List<ExamPlanEntity> executeQueryUsers(String sql)
			throws ClassNotFoundException {
		List<ExamPlanEntity> usersIds = new ArrayList<>();
		Connection conn = getConnection();
		PreparedStatement pst = null;
		ResultSet resultSet = null;
		try {
			pst = conn.prepareStatement(sql);
			resultSet = pst.executeQuery();
			while (resultSet.next()) {
				ExamPlanEntity eplans = new ExamPlanEntity();
				eplans.setId(resultSet.getInt("id"));
				eplans.setName(resultSet.getString("name"));

				usersIds.add(eplans);
			}
			return usersIds;
		} catch (SQLException e) {
			throw new DaoRuntimeException("补考人员名单的请求数据库错误！");
		} finally {
			close(resultSet, pst, conn);
		}
	}

	/**
	 * 获取用户ID
	 * 
	 * @param sql
	 * @return
	 */
	public static List<ExamPlanEntity> userId(String sql) {
		try {
			List<ExamPlanEntity> usersIds = new ArrayList<>();
			Connection conn = getConnection();
			PreparedStatement pst = null;
			ResultSet resultSet = null;
			try {
				pst = conn.prepareStatement(sql);
				resultSet = pst.executeQuery();
				while (resultSet.next()) {
					ExamPlanEntity eplans = new ExamPlanEntity();
					eplans.setOperate_user_id(resultSet
							.getInt("operate_user_id"));
					usersIds.add(eplans);
				}
				return usersIds;
			} catch (SQLException e) {
				throw new DaoRuntimeException("补考人员名单的请求数据库错误！");
			} finally {
				close(resultSet, pst, conn);
			}
		} catch (ClassNotFoundException e1) {
			throw new DaoRuntimeException("数据库加载驱动错误！");
		}
	}

	/**
	 * 新增数据回显
	 * 
	 * @return
	 */
	public static List<ExamPlanEntity> BackList(String sql) {

		try {
			List<ExamPlanEntity> usersIds = new ArrayList<>();
			Connection conn = getConnection();
			PreparedStatement pst = null;
			ResultSet resultSet = null;
			try {
				pst = conn.prepareStatement(sql);
				resultSet = pst.executeQuery();
				while (resultSet.next()) {
					ExamPlanEntity eplans = new ExamPlanEntity();
					eplans.setId(resultSet.getInt("id"));
					eplans.setName(resultSet.getString("name"));
					usersIds.add(eplans);
				}
				return usersIds;
			} catch (SQLException e) {
				throw new DaoRuntimeException("补考人员名单的请求数据库错误！");
			} finally {
				close(resultSet, pst, conn);
			}
		} catch (ClassNotFoundException e1) {
			throw new DaoRuntimeException("数据库加载驱动错误！");
		}

	}

	/**
	 * 关闭资源
	 * 
	 * @param parameters
	 */
	private static void close(Object... parameters) {
		try {
			if (parameters != null && parameters.length > 0) {
				for (Object obj : parameters) {
					if (obj instanceof ResultSet) {
						((ResultSet) obj).close();
					}

					if (obj instanceof PreparedStatement) {
						((PreparedStatement) obj).close();
					}

					if (obj instanceof Connection) {
						Connection conn = (Connection) obj;
						if (conn != null && !conn.isClosed()) {
							conn.close();
							conn = null; // 销毁内存连接对象
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
